package com.hrp.service;

import com.hrp.common.JdbcUtils;
import com.hrp.controller.LoginController;
import com.hrp.domain.Jxkhzq;
import com.hrp.hrpdao.JxkhzqDao;
import com.hrp.jiradao.TestDao;
import org.apache.ibatis.annotations.Select;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by Administrator on 2018/8/8 0008.
 */
@Service
@Transactional(propagation = Propagation.REQUIRED,isolation = Isolation.DEFAULT,timeout=36000,rollbackFor=Exception.class)
public class JxkhzqService {
    private static final Logger logger = LoggerFactory.getLogger(JxkhzqService.class);


    @Autowired
    private JxkhzqDao jxkhzqDao;

    @Autowired
    private TestDao testDao;



    public List<Jxkhzq> getAllKhzq() {
        return jxkhzqDao.getAllKhzq();
    }

    public Map<String, Object> getJxkhzqByPage(Integer page,Integer rows) {
        Map<String, Object> jsonMap = new HashMap<String, Object>();//定义map
        try{
            Integer total = jxkhzqDao.getTotalCount();
            //List<Jxkhzq> list = jxkhzqDao.getJxkhzqByPage(10,20);

            Integer startIndex = (page-1)*rows;
            Integer endIndex = page*rows;
            List<Integer> listStr = new ArrayList<>();
            for(Integer i=(startIndex+1);i<=endIndex;i++){
                listStr.add(i);
            }
            List<Jxkhzq> list = jxkhzqDao.getJxkhzqByPage(listStr);
            jsonMap.put("total", total);//total键 存放总记录数，必须的
            jsonMap.put("rows", list);//rows键 存放每页记录 list
            return jsonMap;
        }catch (Exception e) {
            e.printStackTrace();
            logger.info("获取分页账期数据失败");
            return jsonMap;
        }
    }


    public void saveKhzqData(Jxkhzq jxkhzq){
        jxkhzqDao.saveKhzqData(jxkhzq);
    }



//    public void excutingzq(Integer zqid){
//        Map<String,Integer> map = new HashMap<>();
//        map.put("zqid",zqid);
//        Integer count = jxkhzqDao.excutingzq(map);
//        System.out.print(count);
//
//    }





       //sql server 2008 数据库本地地址
    private static final String URL = "jdbc:sqlserver://222.222.22.196\\HIS;databasename=THIS4";
    private static final String USER = "hissa";    //登陆身份
    private static final String PWD = "sql2k8";   //验证密码

   public void excutingzq(Integer zqid){

        //JdbcUtils jdbcUtils = new JdbcUtils();

        //String sql1 = "select id,name from YY_KSBMK ";
        String sql1  ="exec usp_gh_ghrctj '2018092300:00:00','2018092323:59:59'";
        List<Object> params = new ArrayList<Object>();
        try{
            List<Map<String,Object>> list = JdbcUtils.findModeResult(sql1,params);
            System.out.println("sql1"+list);
        }catch (Exception e){
            e.printStackTrace();
        }

        Connection conn = null;
        try {
            //1.反射加载驱动程序类
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        } catch (ClassNotFoundException e) {
            System.out.println("未发现类..");
        }
        try {
            //2.通过驱动类DriverManager获取与数据库的连接
            conn = DriverManager.getConnection(URL,USER,PWD);
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("无法连接数据库..");
        }
        try {
            //3.得到连接conn后，获取可执行sql语句的Statement对象
            Statement stmt = conn.createStatement();
            //4.执行sql语句，获取查询结果，并输出
            String sql = " select id,name from YY_KSBMK ";
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()){
                String id = rs.getString("id");
                String pwd = rs.getString("name");
                System.out.println(id+","+pwd);
            }
        } catch (SQLException e) {
            System.out.println("sql语句执行出错..");
        }finally {
            if(conn != null){
                try {
                    conn.close();   //5.关闭数据库连接
                } catch (SQLException e) {
                    System.out.println("数据库连接关闭出错..");
                }
            }
        }


//        Map<String,Integer> map = new HashMap<>();
//        map.put("zqid",zqid);
//        Integer count = jxkhzqDao.excutingzq(map);
//        System.out.print(count);

        //List<Map<String, Object>> list = testDao.getKslist();
        // System.out.print(list);

    }


}
